DataLinkCognition_%E5%89%AF%E6%9C%AC.jpg

导航器：跳至底部



个人项目展现

by Python, Excel, SPSS and [HTML&CSS]

一. 电影数据分析

跳转至项目2

总体目标：

  • 考察“豆瓣电影top250排行榜”的结构和效用，分析的数据价值。

具体目标：

  • [x] 检验该排行榜的客观性与真实性
  • [x] 分析上榜电影的性质
  • [x] 建立各指标对TOP排名的影响模型
  • [x] 寻找电影的“黄金年份”
  • [x] 探索概述呈现与短时记忆的关系

1.数据爬取与清洗

返回导航

从网页爬取数据并清洗，进行初步分析后添加外置修改模块。

1.1.批量获取网页数据并进行初步清洗

Python代码如下：

In [ ]:
import urllib.request,urllib.error 
from bs4 import BeautifulSoup 
import re 
import pandas as pd
In [ ]:
def main(): # 流程控制
    baseurl="https://movie.douban.com/top250?start=" # 目标url的模板
    dataList=getData(baseurl)
    
    savepath="./source/top250.xlsx" # 采用相对路径保存
    saveData(dataList,savepath)
In [ ]:
def askUrl(url):
    head={ 
        "User-Agent":"Mozilla / 5.0(Windows NT 10.0; WOW64) AppleWebKit / 537.36(KHTML, like  Gecko) Chrome / 86.0  .4240  .198  Safari / 537.36"
    } # 模拟浏览器头部，使用用户代理信息
    request=urllib.request.Request(url,headers=head) # 用Request封装url和header作为request对象
    
    html=""
    
    try: # 监控爬取异常
        response=urllib.request.urlopen(request) # 通过request对象返回response对象
        html=response.read().decode('utf-8') # 读取并解码
#         print(html)
    except Exception as result:
        if hasattr(result,'code'): 
            print(result.code) # 异常包含code项时返回相关信息
        if hasattr(result,'reason'):
            print(result.reason) # 异常包含reason项时返回相关信息
    return html
In [ ]:
# 构建目标信息的正则表达式模板

# 详情链接：
findLink=re.compile(r'<a href="(.*?)">') 
# 海报链接：
findImg=re.compile(r'<img.*src="(.*?)"',re.S) # 无视换行符
# 片名：
findTitle=re.compile(r'<span class="title">(.*)</span>')
# 评分：
findRating=re.compile(r'<span class="rating_num" property="v:average">(.*)</span>')
# 评价人数：
findJudge=re.compile(r'<span>(\d*)人评价</span>')
# 概述：
findInq=re.compile(r'<span class="inq">(.*)</span>')
# 相关内容：
findBd=re.compile(r'<p class="">(.*?)</p>',re.S)

def getData(baseurl):
    
    dataList=[] # 用dataList保存全部信息
    
    for i in range(0,10): # 爬取10次/页
        url=baseurl+str(i*25) 
        html=askUrl(url) # 把获取到的网页源码保存到html变量
        
        # 解析数据:
        soup=BeautifulSoup(html,'html.parser')
        for item in soup.find_all('div',class_='item'): # 遍历一页内所有item类，每个item类包含一部电影的全部信息
            
#             print(item)
            data=[] # 用data保存各个信息
            item=str(item) # 防止错误：expected string or bytes-like object

            link=re.findall(findLink,item)[0] # 在item中根据正则表达式finLInk返回目标信息列表，提取link
            data.append(link) # 把link添加至data

            img=re.findall(findImg,item)[0]
            data.append(img)

            title=re.findall(findTitle,item) 
            if len(title)==2: # 既有中文名也有外文名
                ctitle=title[0] # 提取中文名
                data.append(ctitle)
                etitle=title[1].replace('/','')
                etitle=etitle.replace('\xa0','') # 去掉无关符号
                data.append(etitle)
            else: 
                data.append(title[0]) # 提取中文名
                data.append(' ') # 为保证生成表格的一致性，外文名处留空

            rating=re.findall(findRating,item)[0]
            data.append(rating)

            judge=re.findall(findJudge,item)[0]
            data.append(judge)

            inq=re.findall(findInq,item)
            if len(inq)!=0:
                inq=inq[0].replace('。','') # 去掉句号
                data.append(inq)
            else: # 概述缺失时留空
                data.append(' ')


            bd=re.findall(findBd,item)[0]
            bd=re.sub("<br(\s+)?/>(\s+)?","",bd) # 用正则替换保守地（0/1）去掉br标签内外的空白符
            bd=bd.strip() # 去掉前后空格
            bd=bd.replace('\xa0','')
            data.append(bd)

            dataList.append(data) # 把data放入datalist


#     print(dataList)
    return dataList
In [ ]:
def saveData(dataList,savepath):
    dataList=pd.DataFrame(dataList,columns=['详情链接','海报链接','中文名','外文名','评分','评价人数','概述','相关内容'])
    dataList.insert(0,column='排名',value=range(1,251)) # 添加排名列
    dataList.to_excel(savepath,index=False)
In [ ]:
if __name__ == '__main__':
    main()
    print('complete')

处理完成，效果如下： top250.jpg

对获取的数据作进一步处理

主要使用Excel。

① 对电影外文名的有无进行判断标注：

  • 判断公式（未去空格文本）：=IF(SUBSTITUTE(E2," ","")="",0,1)；=IF(TRIM(E2)="",0,1)

② 对电影概述的有无进行判断标注，分析并计算概述内容所包含的信息组块数：

  • 判断公式（已去空格文本）：=IF(ISBLANK(I2),0,1)
  • 计数公式：=ROUNDDOWN(LENB(ASC(I2))/2,0)
    • 文字是表达信息的基础符号，每单位视为1个信息组块；
    • 标点是书面信息的组成部分，参与信息含义的构建，具有形式意义而缺乏内容意义，因此每单位视为0.5个信息组块。
    • 标点符号用于标明书面信息的句读和语气，对信息传递存在边际效用且随单位量的增加呈递减趋势。为了还原这一点并保证信息组块的整数性，我们使用ROUNDDOWN函数对计算的最终结果向下取整。
    • 实现方法：原数据为双字节的全角字符串，使用ASC函数对标点符号进行全-半角转换后，总字节长度的二分之一即为符合定义的信息组块数。

③ 对【相关内容】项进行数据分离：

  • 分离出电影年份；
  • 分离出电影所属国家或地区；
  • 分离出电影类型标签，并根据豆瓣官方发布的全部类型标签进行判断标注。
    • 判断公式：=IF(ISNUMBER(FIND(Q$1,$P2)),1,0)

处理完成，效果如下： top250-new.jpg

添加外置修改模块

在实际操作过程中，我们可能会因为某些意外而产生额外的数据需求。比如在本项目中，我们观察到部分电影虽然别名判断为0，但实际上具有外名，也就是说我们在提取这些电影的信息时发生了缺失，从而引发了判断上的错误，这就需要我们重新定位合适的数据源并进行相应的“增删改查”。

当意外发生时，我们通常希望在已处理好的工作表上修改，而非生成另一张未经处理的新表，尤其是在那些生成时间偏长的项目中。当修改完成后，我们通常希望能够保留修改程序的可移植性和可重用性，以达到“高内聚、低耦合”的效果。

鉴于此，我们选择尽可能地不在原程序上动工，而是添加相应的外置修改模块。这种模块并非必要，但却是构建理想模型的一部分。

针对本项目中出现的意外，我们可以通过分析网页源代码来发现问题的根源。分析显示，网页设计者并没有为全部电影添加专门储存外文名的第二个title类，而是将部分电影的外文名直接储存在了other类中，这就导致我们无法只通过title类来提取所有电影的外文名。这种情况多出现在国产电影中。

或许在网页设计者看来国产电影的外文名并不是那么重要，但为了数据的准确性，我们应当采取更加全面的提取方式，将缺失的单元格补上并修正判断结果。由于涉及到既定项目格局下的单元格级别的操作，因此考虑引入openpyxl模块。

修改模块的Python代码如下：

In [ ]:
import urllib.request,urllib.error 
from bs4 import BeautifulSoup 
import re 
import pandas as pd

import openpyxl as op
import string
In [ ]:
baseurl="https://movie.douban.com/top250?start="
findtitle=re.compile(r'<span class="title">(.*)</span>') # 用于提取片名的正则表达式（作为字典的键）
findother=re.compile(r'<span class="other">(.*)</span>') # 用于提取缺失外文名的正则表达式（作为字典的值）
searchdict={} # 定义字典用于查询

def askurl(url): 
    head={ 
        "User-Agent":"Mozilla / 5.0(Windows NT 10.0; WOW64) AppleWebKit / 537.36(KHTML, like  Gecko) Chrome / 86.0  .4240  .198  Safari / 537.36"
    }
    request=urllib.request.Request(url,headers=head)
    response=urllib.request.urlopen(request)
    html=""
    html=response.read().decode('utf-8')
    return html

def getdata(baseurl):
       
    for i in range(0,10): 
        url=baseurl+str(i*25) 
        html=askurl(url)     
        soup=BeautifulSoup(html,'html.parser')
        
        for item in soup.find_all('div',class_='item'): 
            item=str(item)
            
            title=re.findall(findtitle,item)[0] # 提取中文名
            
            other=re.findall(findother,item)[0] # 缺失外文名粗提取
            ename='' 
            for o in other:
                if o in string.ascii_letters: # 精确提取
                    ename+=o
                            
            searchdict[title]=ename # 存储“中文名：缺失外文名”的键值对

print('start...')
getdata(baseurl)
print('%d complete'%len(searchdict),end='\n\n') # 完成检查器
# print(searchdict)
In [ ]:
# 读取处理对象：
wb=op.load_workbook('E:/JupyterNb/source/top250-new%.xlsx')
sheets = wb.sheetnames 
wb1 = wb[sheets[0]]
# print(wb1.cell(row=1,column=6).value)
In [ ]:
for i in range(1,251):
    val=wb1.cell(row=i+1,column=6).value # 遍历外文名列并提取值
      
    if val=='' or val[0:1].isspace() : # 检测空白单元格（空值or白值）
#         print(val,'#',i)
        key=wb1.cell(row=i+1,column=5).value # 将对应的中文名作为key
        wb1.cell(row=i+1,column=6).value=searchdict[key] # 通过key返回value并对空白单元格赋值
        
wb.save('E:/JupyterNb/source/top250-upgrade.xlsx')
print('ok')

以上是本次修改的主要内容，其它修改内容还包括：

  • 为防止歧义，修改“别名”为“外名”；
  • 用淡蓝色块区分出非主要指标；
  • 添加“排名系数”项作为辅助指标，用深蓝色标识，其值为“251-排名”；
  • 添加“打包”项作为辅助指标，用深蓝色标识，50个为一组

修改完成，最终效果如下： top250-upgrade.jpg

数据分析

返回导航

针对处理好的数据，使用SPSS和Excel进行分析。

榜单的客观性与现实性

  • 使用SPSS分析“评分”项数据的平均值、标准差和频数分布；
  • 绘制频数分布直方图，并显示相同标准下的正态曲线；
  • 绘制P-P图和Q-Q图，用于分布情况的检验。

%E7%9B%B4%E6%96%B9%E5%9B%BE.png

PQ%E5%9B%BE.png

  • 观察直方图，数据近似服从正态分布，但不够拟合；
  • 进一步观察P-P图和Q-Q图，可见散点基本呈对角直线分布，表现出明显的正态性。

由此可得：该榜单中的评分数据近似服从正态分布，是许多独立随机因素影响的结果，因此具有一定的客观性和现实性。

上榜电影的性质

对从TOP榜单中提取的各项电影的主要指标进行分析，以期展现上榜电影的特征与性质。

① 标度指标分析

使用SPSS对榜单中主要标度指标的分布趋势进行描述性统计：

平均值 标准差 最小值 25% 50% 75% 最大值 众数 变异系数
评分 8.9 0.3 8.3 8.7 8.9 9.1 9.7 8.7 0.034
评价人数 684680 420040 106217 401426 567174 851796 2646665 多个 0.613
年份 2001 16 1931 1994 2004 2011 2020 多个 0.008

评分的均值为8.9，四分位点为8.7、8.9、9.1，两两差值为0.2；其中25%位点高出最小值0.4，50%位点即中位数等于均值，75%位点低于最大值0.6；结合标准差为0.3，变异系数为0.034，可以看出该指标具有较强的集中趋势和较弱的离散趋势，各电影间区别不算明显，这一点可以通过箱形图得到进一步验证: %E7%AE%B1%E5%9B%BE_%E5%89%AF%E6%9C%AC.png

值得一提的是，豆瓣网站内的电影评分总体处于2-10之间，基本服从正态分布，平均分为6.8:

https://zhuanlan.zhihu.com/p/24815577
https://zhuanlan.zhihu.com/p/40645725 %E8%B1%86%E7%93%A3%E7%94%B5%E5%BD%B1%E8%AF%84%E5%88%86%E6%80%BB%E4%BD%93%E5%88%86%E5%B8%83.jpg

在这种背景下，该榜单内的电影评分围绕8.9集中，可以看出评分指标对榜单的入选资格具有较大影响，推测其对榜单内的排名顺位亦具有较大的影响。这点将在后面做进一步验证。

评价人数的均值为70万，标准差却达到了40万，四分位点之间、25%位点与最小值之间的差值以十万计，75%位点与最大值的差值却达到了180万，再结合其高达0.613的变异系数（显著高于评分系数的0.034），可以得出该指标具有很强的离散趋势。

但我们能否据此认为该指标在各电影间的区别很大呢？先看一下相应的箱形图： %E7%AE%B1%E5%BD%A2%E5%9B%BE-%E8%AF%84%E4%BB%B7%E4%BA%BA%E6%95%B0.png

从图中可以观察到，该数据集中含有不少离群值，大部分集中在[Q3+1.5IQR]之外，有一个甚至处于[Q3+3IQR]之外（为第一个数据，即排在第一位的那部电影的评价人数），而在最低值至75%位点之间，尤其是25%位点至50%位点之间的数据的离散程度则不是很明显。

在该分析项目中，我们无法剔除这些离群数据，甚至越极端的数据（比如排名第一的电影）越难以忽视。因此我们可以认为，该指标的离散趋势之所以这么强，是因为许多电影的评论人数过于突出，以至于表现出明显的分化趋势。

年份的均值为2001，标准差为16，四分位数为1994、2004、2011，变异系数为0.08，可以说相差不大。

但是年份最早的1931，与25%位点相差了足足63年，可见TOP电影的产出经历了相当一段时间的沉寂期，具体表现见如下条形图： %E6%9D%A1%E5%BD%A2%E5%9B%BE-%E5%B9%B4%E4%BB%BD.png

不难看出，自从1931年诞生了第一部TOP电影开始，TOP电影的产量在近半个世纪内都维持在一个很低的水平。

但在在进入八十年代后，TOP电影的产量开始表现出明显的上升趋势，并在1994年迎来了第一个高峰（诞生了13部TOP电影），之后便进入了高产期，并于2004年和2010再度迎来相同的高峰。1994、2004、2010三个数字也同时构成了年份指标中的三个众数。

评价人数和年份的众数均为多个，但显然年份的众数更有意义，不仅是因为其彰显了电子信息技术的飞速发展在电影行业内的映射，还因为这一指标可以显示出哪些年份的电影占据了TOP榜单中最多的份额，从而为寻找电影的“黄金年代”提供参考。

② 名义指标分析

使用Excel数据透视表进行分析：

国家归属指的是一部电影所属的国家。

我们可以很容易地知道一部电影的制片公司以及制片地区，但却并不总是能顺利地确定一部电影的国家归属，因为这些公司和地区时常不是唯一的。

如果了解详细情况不是必须的话，比较高效的方式是将其单独归为一类并重新赋名。于是我在此规定：倘若一部电影有多个制片国家，则将其“国家归属”指标显示为“多个”。

在“国家”指标项中，多个国家名之间使用空格进行分隔，这为我们判断其“国家归属”是否为“多个”提供了便利。但在无法确定单一国家名称前后是否含有空格的情况下，直接依据空格的有无进行判断显得有些不够谨慎，所以我们可以利用含有通配符表达式的“IF-SEARCH”公式【=IF(ISNUMBER(SEARCH("? ?",P2)),"多个",P2)】来进行判断，这样会更加可靠且更具有普适性。

判断完成后，用数据透视表对“国家归属”项下的各个数据进行计数聚合，得出如下频数表并作图： %E5%9B%BD%E5%AE%B6%E5%BD%92%E5%B1%9E.png

图中可以看出，上榜的TOP电影中最多的是美国电影，占比30.8%；其次是中国和日本，分别是14.8%和13.2%；接着是韩国，占比4.4%。另外也有很多TOP电影出自多个国家之手，这种模式的占比为30.4%。

在作进一步判断之前，我们需要明确一点：这些数据能够说明一些国家具有生产优秀电影的绝对能力，但却不足以说明这些国家具有生产优秀电影的相对能力。要想衡量相对能力，还需要了解该国生产的电影总量，并计算出优秀电影数量占总数量的比例。

除了定义本身的支撑外，该结论还基于这样一个逻辑：高绝对产出的背后既可以是“少而精”的质量路线，也可以是“多而平”的数量路线，但相对产出的提高，则唯有依赖“少而精”的质量路线才能得以实现。

外名指的是一部电影除中文名以外的名字。理论上应该每一部上榜电影都有，但实际情况存在例外。

由于已事先对判断结果进行了数值化（0/1），因此我们可以利用Excel数据透视表中的求和项对缺失情况进行数量上的聚合分析： %E5%A4%96%E5%90%8D%E6%9C%89%E6%97%A0-4.png

分析显示，有外名的电影共245部，占比为98%，外名缺失的电影共5部，占比2%。

添加各国上榜电影数量作对比，降序排序后观察可知，外名缺失的电影主要分布在高产国之间，其中有3部为中国电影。

由于数量不多，我们可以直接定位到具体电影。基于“国家（区域细分）”项和“中文名”项对“外名有无”项为0（即外名缺失）的电影进行展开分析，可以看出，外名缺失的电影全部与亚洲相关，分别是：《告白》、《让子弹飞》、《我不是药神》、《色，戒》和《倩女幽魂》。

为何会产生缺失呢？我们以《让子弹飞》为例进行探索：

%E8%AE%A9%E5%AD%90%E5%BC%B9%E9%A3%9E-2.jpg

仔细观察可以发现：在排行榜页面中，该电影的名称区块（也就是网页源代码中的title类和other类）并未显示出外文名，但在详情页面的“又名”项的末位，却显示出了该电影的英文名（即“Let The Bullets Fly”）。

也就是说，所谓的“外名缺失”是展现意义上的缺失，而非实际意义上的缺失，是网页设计的问题，而非值得研究的问题。

经考察，其它四部电影均是如此，这里就不一一赘述了。

概述指的是对某个电影的一句话描述，位于排行榜页面中电影单元的最下方。

不是每个电影都有概述。通过对概述的有无进行判断，我们发现其存在一种减少的趋势。

为了进一步了解情况，我们使用50个为一组的“打包”项来对0/1判断式的“概述有无”项进行求和聚合，其值即为每个分段内包含概述条目的电影的数目： %E6%A6%82%E8%BF%B0.png

以打包组为单位，按照排名从小到大排列，可以看出虽然概述缺失的电影只占总体的5.2%，但其随着排名的推后具有比较明显的数量上升趋势（也就是图中所呈现出的非缺失电影的数量下降趋势）。

由于榜单外的电影详情页面中并不存在所谓的概述模块，因此我们有理由认为该模块是在榜单生成后特意添加的，也就是说这一指标虽然和其他外显指标一同展现，但其并不为入选资格的研判提供参考价值。

按照这一思路，我们可以对缺失情况背后的动机作如下推测：概述是为了点出一部作品的亮点从而达到一句话推荐的效果，随着电影排名的推后，电影的质量和曝光都会有所降低，利用一句话概述进行电影推荐的必要性也会有所下降。

类型标签共有22个，表征了一部电影所击中的电影类型。在豆瓣的语境下，一部电影的类型标签通常不是唯一的。

依据0/1式判断标注求出TOP榜单中含有某标签的电影的数量及其占比，做出相关列表和图表并按降序排序： %E6%A0%87%E7%AD%BE%E8%81%9A%E5%90%88-3.png

可以看出，榜单电影的类型标签中最多的为“剧情”标签，含有该标签的电影占总电影比例为75.2%，数量上远高于其它标签；其次是“爱情”、“喜剧”、“冒险”、“犯罪”和“奇幻”标签，占比分别为22.8%、21.6%、18.4%、16.8%和16.4%。

正如前文所言，在不确定总量的情况下，我们无法单纯根据这些比例来推断含有某个标签的电影的上榜可能（具体总结见下方TIPs）。发掘这类数据更多价值的方法之一，是对这类数据和排名进行相关分析，来辨析该数据到底是均匀分散在榜单中的客观指标，还是跟随排名波动而波动的相关指标。

    TIPs：

值得一提的是，上榜电影所具有的性质分布只能为分析上榜条件的必要性（成为必要条件的概率）提供参考，并不足以为分析上榜条件的充分性提供依据。
举例来说，上榜电影中大部分都含有剧情标签并不一定是因为含有剧情标签更容易上榜，还有可能是因为大多数电影本身便含有剧情标签。要想明确剧情标签对上榜的促进作用，就需要比较所有包含剧情标签的电影中上榜的比率是否显著高于其他标签。

建立各指标对TOP排名的影响模型

首先通过SPSS相关分析确定各指标与排名数据之间的关联程度，然后选择关联程度较高的组合进行回归分析，依据回归分析的结果推导指标对排名的影响公式。

① 相关分析

使用“排名系数”项代替“排名”项参与相关分析：

参与指标： 评分 评价人数 概述有无 年份 国家归属 类型标签
相关类型 Spearman/Pearson Spearman/Pearson Spearman/点二列/Eta方 Spearman Spearman/卡方检验/Eta方 Spearman/点二列/Gamma/Eta方
显著性 0.00/0.00 0.00/0.00 0.00/0.00/... 0.013 0.263/0.433/... 见下表
相关系数 0.749/0.742 0.665/0.655 0.244/0.244/0.06 0.157 0.071/.../0.042 见下表
相关判定 强 较强 较弱 弱 无 见下表

说明：

  • 排名越高的电影“排名系数”越大，这样可以方便相关系数的查看（排名系数的计算方式见1.3部分）。
  • 由于排名系数不服从正态分布，因此其在严格意义上不适用于须双变量符合正态分布的Pearson相关，但在一般情况下可以将Pearson相关作为一种检验的手段（包括使用点二列相关）。
  • 在确定概述是榜单特意添加的前提下，这里的相关实际上揭露的是TOP排名对“概述有无”的影响。
  • 为方便计算，需要对“国家归属”项的数据进行数值化。可以在Excel里添加 [1,14] 的整数数组表并使用VLOOKUP函数【=VLOOKUP(Q2,Sheet2!\$I\\$101:\$J$114,2,0)】构建额外的数值列，亦可以使用SPSS的转换模块对该变量进行重新编码。
  • 虽然排名数据在本质上是计数形式的离散化顺序数据，但因为各数据在 [1,250] 的范围内均匀分布，频数为1，间隔为1，类似于一种连续数据的离散化，所以可在参与部分相关分析时近似视为连续数据。倘若我们规定落入此范围内某整数左右0.5区间内的数值可近似拟合为该整数，那么便更有理由把排名数据当作连续数据来处理了。

由于类型标签数量太多，我们选取数量上较有代表性的前六个标签进行展开分析：

类型标签： 剧情 爱情 喜剧 冒险 犯罪 奇幻
Spearman相关 0.109，0.084 -0.039，0.544 0.04，0.53 0.085，0.18 -0.043，0.496 0.101，0.112
点二列相关 0.109，0.084 -0.039，0.544 0.04，0.53 0.085，0.18 -0.043，0.496 0.101，0.112
Gamma 0.146，0.069 -0.053，0.55 0.056，0.517 0.127，0.155 -0.067，0.492 0.157，0.083
Eta方 0.012 0.001 0.002 0.007 0.002 0.01
相关判定 几乎无 无 无 无 无 几乎无

说明：

  • 双数值单元格的对应含义为“相关系数，显著性”。
  • 当视“排名系数”为因变量时，Somers'd的分析结果与Gamma相同。

经过以上分析，我们可以得出以下结论：

  1. 对“排名系数”指标来说，“评分”和“评价人数”是高相关指标，“年份”是低相关指标，可以利用回归分析来进一步明确指标间的影响方式并计算影响权重。
  2. 方向既定的情况下，排名数据的变化对概述条目的有无确实存在一定影响，这为2.2中针对概述项的分析和推测提供了一定的理论依据。
  3. “国家归属”和“类型标签”指标与排名数据间的关联程度几乎为零，因此可以被视为仅具有描述性质的客观指标。

我们还可以通过Excel数据透视表对相关指标按类求出排名值的聚合平均数，通过折线图来验证相关趋势。

以“评分”指标为例，作出评分类别和各评分类别下平均排名（非排名系数）的折线趋势图，可以看出随着评分的增大，平均排名呈现出明显的下降趋势，只在9.5评分处出现了一些意外，可能与某些优质但冷门的电影有关。 %E8%AF%84%E5%88%86-%E6%8E%92%E5%90%8D.png

② 回归分析

根据相关分析的结果，将“评分”、“评价”、“年份”三个指标作为自变量/协变量，将“排名系数”作为因变量，进行包括线性和非线性在内的回归分析。

本次回归分析的目的主要是建立解释模型而非预测模型，对于数据的独立性和正态性要求比较宽松。为了在此基础之上保证准确度，我们采取多种方法来进行拟合，以此探索并验证最适系数。

为了给回归分析提供一定的参考，我们首先利用相关系数与决定系数之间的数学关系求出R方的大小（R方=$r^2$），以此得出排名系数的变异量中能够被各指标与排名系数的关系所解释的比例有多大：

评分 评价人数 年份
r 0.749 0.665 0.157
R方 0.561 0.442 0.025

a. 散点图矩阵

首先建立四个变量的散点矩阵，对变量间的关系进行初步观察：

%E6%95%A3%E7%82%B9%E5%9B%BE%E7%9F%A9%E9%98%B5.png

经过初步观察，可以得出以下结果：

  • 排名系数与评分间存在线性/曲线关系；
  • 排名系数与评价人数间存在曲线关系；
  • 排名系数与年份间几乎不存在关系，或存在非线性关系；
  • 三个自变量间存在一定的相关性，可能会影响结果。

根据观察结果，我们可以对回归分析的目标进行制定：

  1. 使用多变量回归对变量间关系进行初步估计和研判，其中包括共线性的诊断；
  2. 使用线性回归分析排名系数和评分的关系；
  3. 使用曲线回归分析排名数据和评价人数的关系；
  4. 探索排名数据和年份间的最适拟合线。

b. 多变量回归

采用线性方式对变量间关系进行初步估计和研判，各条件上不作严格要求。

为提高估计的准确性，使用多重线性回归和有序Logistic回归两种方式相互对照。

设评分为 $a$，评价人数为 $b$，年份为 $c$。

--- 多重线性回归：

评分（a） 评价人数（b） 年份（c）
R方 0.740
D-W检验 0.425
方差分析 F=233.528，P=0.000
系数（未标准化） 163.382 7.791E-5 0.101
系数（标准化） 0.601 0.453 0.022
系数显著性 0.000 0.000 0.559
常量 -1588.651
容差 0.703 0.769 0.754
VIF 1.423 1.301 1.327

说明：

  • R方高达0.74，说明模型能够解释因变量74%的变异，属于大效应范畴；数值上大于0.6且略小于0.8，已接近实验性研究的标准。也就是说，尽管我们粗暴地采取了线性回归，但模型却在拟合度上表现很好。
  • D-W值偏小，因变量间的相互独立性不够好。
  • 方差分析的F值=233.528，显著性P<0.05，说明数据波动更多来源于系统因素而非随机因素，模型具有统计学意义。
  • 根据未标准化系数可以得出回归模型，根据标准化系数可以比较自变量对因变量的影响权重，根据系数显著性可以确定其是否具有回归意义。
    • 回归方程：【$y=163.382a+7.791*10^{-5}b+0.101c-1588.651$】
    • 影响权重：评分(0.601) > 评价人数(0.453) > 年份(0.022)，该数据在绝对大小和相对关系上与各指标对排名系数的决定系数很接近。
    • 回归意义：“评分”和“评价人数”的系数显著性小于0.05，具有回归意义；“年份”的系数显著性大于0.05，不具备回归意义。考虑到各自变量与因变量的相关系数大小，这种结果并不意外。一般情况下应当剔除不具备回归意义的项并重新建模，但此阶段的主要目的在于建立解释型模型，为了明确各自变量对因变量的作用大小和方向，可以暂时保留不具备回归意义的项。
  • 各变量容差更接近1而非0，VIF均不大于10，由此可见各变量间相关性不算高，共线性不明显，回归系数的符号和大小基本不存在异常。
  • 离群值的判定标准是其残差在3个标准差之外。这里共有3个离群值，经检查后数据本身无误。考虑到此阶段的估计目的以及数据的真实性，暂不作剔除处理。

--- 有序Logistic回归：

我们先以“排名系数”为因变量进行多个协变量的回归分析：

评分 评价人数 年份
伪R方 1.000
卡方检验 P=0.000
拟合优度检验 P=0.000
平行线检验 P=0.000
参数估计 18.243 1.084E-5 -0.001
参数显著性 0.000 0.000 0.898

对常数项取中位数，得出其回归方程为：【$logit(P)=18.243a+1.084*10^{-5}b-0.001c+165.78$】

表面上看回归分析的各指标都很理想，但显然有些过于理想了。回归线完全拟合，显著性几乎全部为0，一个粗糙的估计式回归模型中出现如此理想的结果，这种情况是很反常的。

问题之根源，就在于数据集的完全分离和拟完全分离。

具体而言：

  • 当协变量的线性组合能够生成完美的响应变量预测时，就会发生数据集的完全分离；
  • 当协变量的线性组合能够在绝大多数情况下生成完美的响应变量预测时，则发生数据集的拟完全分离。

用数学语言来表述整个过程就是：

设有数据集$(x_i,Y_i),i=1,...,n$：

$x_i'=(x_{i0},x_{i1},...,x_{ip})，x_{i0}\equiv1$，n行$x_i'$组成$n*(p+1)$的矩阵，为$X$；

$\{Y_i:i=1,...,n\}$为相互独立的随机变量，$Y_i$的概率质量函数（probability mass function）为：

$P[Y_i=j|x_i]=\dfrac{e^{(a_j'x_i)}}{\sum^g_{t=0}e^{(a_t'x_i)}}，j=0,1,...,g，g\geq1$；

有$a'_j=(a_{j0},...,a_{jp})\in R^{p+1}，1\leq j \leq g，a_0\equiv0$，设$a'=(a'_1,...,a'_g)\in R^{(p+1)g}$；

用$j(i)$表示结果$j$，$j(i)\in [0,g]$，则有$Y_i=j=j(i)$。

设$t\in \{0,...,g\}$ \ $\{j(i)\}$；

则$a_{j(i)}'=a_j'=(a_{j0},...,a_{jp})$，$a_t'=(a_{t0},...,a_{tp})$；

则$(a_{j(i)}-a_t)'=(a_{j0}-a_{t0},...,a_{jp}-a_{tp})=A_{1*(p+1)}$；

已知$x_i'=(1,x_{i1},...,x_{ip})$，则$x_i=A_{(p+1)*1}$；

则$(a_{j(i)}-a_t)'x_i=A_{1*1}$；

当存在$a\in R^v$，对于每一个$i=1,...,n$，都有$(a_{j(i)}-a_t)'x_i>0$时（即该向量能够正确分配所有观测值至它们的组），则称该数据集完全分离；

当存在$a\in R^v$，对于每一个$i=1,...,n$，都有$(a_{j(i)}-a_t)'x_i\geq0$时（即该向量能够正确分配几乎所有观测值至它们的组），则称该数据集拟完全分离。


 参考文献：

  • Adelin Albert, J. A. Anderson. On the Existence of Maximum Likelihood Estimates in Logistic Regression Models. April 1984, Biometrika 71(1).
  • Thomas J Santner, Diane E. Duffy. A Note on A. Albert and J. A. Anderson's Conditions for the Existence of Maximum Likelihood Estimates in Logistic Regression Models. December 1986, Biometrika 73(3).


通过分离的完整定义。我们可以推测其在通常情况下的发生原因（条件）：

  1. 数据集太小：数据集越小，解释变量各类别内仅有一个响应值的概率就更高，也就越能够寻找到这样一种条件，使得不同条件下的解释变量能够各自生成对其响应变量的预测（即按响应变量划分的各组变量间具有显著的组间差异，数据总体分布不均衡，相同的解释变量难以与不同的响应变量相对应）。
  2. 解释变量太多：模型中的解释变量越多，某种划分条件下至少有一个解释变量发生分离的可能性就越大(即各解释变量均不分离的可能性越小)。
  3. 变量未进行足够的分组合并：合并类别越多，各类别组内变量越少，数据分布也就越难均衡。在一一对应（即排除了“多对一”的映射）的极端情况下，各组内变量均不相同。

用一句话来概括就是：数据分布越不均衡，分离的情况就越容易发生。

了解了分离的含义和来源后，接下来需要明确的是分离对Logistic回归的影响。

在Logistic回归模型中，两种分离情况都会阻止参数的最大似然估计值的收敛，使得对数似然函数会随着参数的增加而无限趋近于某个固定值。在完全分离情况下，这个固定值为0；在拟完全分离情况下，这个固定值为非0常数。

当对数似然函数随着参数的增加无限趋近于某值而不会下降时，视参数为变量对其求偏导的结果将始终无法为0。也就是说，我们无法求出此时对数似然函数的最大值，也就无法通过最大似然估计来推断线性组合的具体参数。

为了明确目标数据集的详细分离情况，我们先用对各变量组合进行有序Logistic回归考察：

协变量： 三元 评分&评价人数 评分&年份 评价人数&年份 评分 评价人数 年份
分离诊断 完全分离 完全分离 无 无 无 无 无
-2对数似然比 0.000 0.000 2376.913 2529.963 1358.748 2572.836 2161.494

可以看出：

有序Logistic回归在本质上依然是二项Logistic回归，其转换方式是依次将因变量按不同的取值水平分割成两个等级。当我们以“排名系数”为因变量对三个协变量进行有序Logistic回归时，因为协变量数量过多，因变量合并程度不够的同时又引入了较多的高相关指标（高相关指标会加剧数据分布的不均衡），所以使得整个数据集发生了完全分离或拟完全分离，进而使得迭代（收敛）后的对数似然值实际为0，-2对数似然比（也就是似然比G检验）为0，最大似然估计值不存在。

所以，为了让有序Logistic回归成功进行，我们需要针对分离的发生原因对源数据进行一些预处理：

  • 就数据集大小而言，该榜单是完全采集，没有办法再增加更多的数据；
  • 就解释变量数量而言，三个变量都是经过相关分析筛选而来的，务必都要考虑在内（尤其是高相关指标）；
  • 就变量的分组合并而言，该数据集实际上是依据“排名系数”项分成了250个组，组数过多且合并程度不够。如果能够在此基础上作进一步的分组合并，就有可能使数据集中的分离状况消失。

所以我们接下来的目标就是寻找某种具有较高合并程度的分组变量作为因变量，使得数据集在该因变量的各组间的具有更小的组间差异，进而使得数据集的分布更加均衡，直到能够消除数据分离为止。

观察数据集，发现将数据分成5（*50）组的“打包”项很适合作为新的因变量。为了便于处理，我们使用SPSS的转换功能将“打包”项重新编码为“打包数值”项。该项将文本数据转化为数值，顺序为降序。

接下来，我们将“打包数值”项作为因变量，对“评分”、“评价人数”、“年份”三个协变量进行有序Logistic回归分析。

结果如下：

评分 评价人数 年份
-2对数似然比 最终：25.487
卡方检验 P=0.000
拟合优度检验 皮尔逊：P=0.000
伪R方 0.837,0.872,0.563
参数估计 16.475 1.178E-5 0.009
参数显著性 0.000 0.000 0.368
常数项 167.676/170.365/173.296/178.567

从中可以看出，-2对数似然比不再为0，伪R方等指标更加贴近真实情况，这证明新模型在消除数据分离的影响上已经有了较大的改善。

对常数项取平均，得出该回归方程为：【$logit(P)=16.475a+1.178*10^{-5}b+0.009c+172.476$】（P为二分结果中某结果的后验概率）。

根据各显著性和伪R方结果，可以看出该模型具有较好的拟合度和有效性。

c. 单变量回归

结合散点图，对因变量（排名）与单个自变量建立回归模型，以期进一步探索各指标对TOP排名的影响。

  • 评分-排名：

观察“评分-排名”的散点图，推测适用于线性拟合或曲线拟合，且线性拟合的可能性更大。

分别采用线性模型、对数模型和二次模型对变量组合进行回归拟合，结果如下：

线性模型 对数模型 二次模型
R方 0.551 0.553 0.561
F值 303.985 307.045 157.922
显著性 0.000 0.000 0.000

三种模型的R方均大于0.25且大于0.5，说明模型解释度较高；F值均显著，说明存在处理效应。

结合图像（第一张图），可以得出：三种回归模型均有一定效度，且在变量的有效范围内差距并不显著（其中线性模型和对数模型几乎完全重合）。

%E8%AF%84%E5%88%860.png

当曲线回归对比线性回归没有明显的优势时，我们直接采用线性回归即可。

我们首先对全部数据进行线性回归，建立回归模型（第二张图）。倘若我们将每次回归分析时残差处于3个标准残差（或估计标准误）之外的值定义为离群值（异常值），持续剔除并重新分析直至不包含任何离群值，这样就能在剔除了5个离群值之后得到一个新的回归模型（即第三张图）。

两种线性回归的分析结果对比如下：

线性回归： 包含离群值 剔除离群值
R方 0.551/0.549 0.648/0.647
D-W检验 0.9 1.089
F值显著性 0.000 0.000
系数 201.870 220.694
系数显著性 0.000 0.000
常量 -1675.986 -1840.537

可以看出：

  • R方>0.5，说明模型都具有不错的解释度和拟合度，但剔除后的表现更好；
  • DW<1.8，说明响应变量的独立性不太够，残差存在一定的自相关，不过剔除后的表现要相对更好；
  • 各显著性P<0.05，说明模型具有统计学意义（通常表现为拒绝零假设）。

据此，我们得出“评分-排名”的回归方程为：

  • 包含离群值：【$y=201.87x-1675.986$】
  • 剔除离群值：【$y=220.694x-1840.537$】
  • 评价人数-排名：

观察“评价人数-排名”的散点图，推测适用于线性拟合或曲线拟合，且曲线拟合的可能性更大。

分别采用线性模型、对数模型和二次模型对变量组合进行回归拟合，结果如下：

线性模型 对数模型 二次模型
R方 0.429 0.450 0.462
F值 186.568 203.271 105.937
显著性 0.000 0.000 0.000

三种模型的R方均大于0.25且接近0.5，说明模型具有不错的解释度，其中对数模型和二次模型的解释度相对更高；F值均显著，说明存在处理效应，其中对数模型的F值最大。

观察图像（第一张图），可以看出虽然二次模型在变量范围内的拟合度不错，但由于其在顶端具有拐点，不符合上升的基本趋势，故排除该模型的使用。

%E6%9B%B2%E7%BA%BF%E5%9B%9E%E5%BD%92%E6%80%BB%E8%A7%88-2.png

剩下的两类模型中，无论是根据参数指标还是图像表现，对数模型都具有明显的优势，故选用对数模型进行回归拟合。

根据参数估计的结果，可以得出回归方程为：【$y=83.762logx-986.141$】

  • 年份-排名：

观察“年份-排名”的散点图，没有明显的拟合趋势，其影响方式在一定程度上与随机变量无异。考虑到两变量的低相关性，这种情况并不意外。

此时可以考虑剔除“年份”变量，但若执意将该变量纳入影响模型的话，可以在承认缺乏拟合趋势的前提下，采用最小二乘法寻找一条相对最适的拟合线。

经拟合，该最适回归方程为：【$y=-0.446x+1016.946$】

d. 模型提炼和比对

根据以上分析，我们对与解释变量相关的各系数指标和回归方程进行集中展现：

系数指标↓ 评分 评价人数 年份
相关系数 0.749 0.665 0.157
决定系数 0.561 0.442 0.025
线性回归系数-多重 163.382 7.791E-5 0.101
线性回归系数-多重（标准化） 0.601 0.453 0.022
线性回归系数-单值 201.870 >> 220.694 -0.446
曲线回归系数-对数 83.762
有序Logistic回归系数-排名系数 18.243 1.084E-5 -0.001
有序Logistic回归系数-打包数值 16.475 1.178E-5 0.009


 回归方程：

  • 多变量回归：$y=163.382a+7.791*10^{-5}b+0.101c-1588.651$
  • 有序Logistic回归：
    • 排名系数：$logit(P)=18.243a+1.084*10^{-5}b-0.001c+165.78$
    • 打包数值：$logit(P)=16.475a+1.178*10^{-5}b+0.009c+172.476$
  • 单变量回归-评分：
    • 包含离群值：$y=201.87x-1675.986$
    • 剔除离群值：$y=220.694x-1840.537$
  • 单变量回归-评价人数：$y=83.762logx-986.141$
  • 单变量回归-年份：$y=-0.446x+1016.946$

我们的目标是建立各指标对TOP排名的影响模型，简称排名生成模型。为了构建足够多的模型用于验证和比对，我们先对以上信息进行聚合分析。

  • 系数指标分析：

    • 相关系数、决定系数和标准化回归系数适用于比较各解释变量对响应变量的影响力（权数），但由于各变量的衡量标准不同，所以需要配合变量的标准化来建立权数模型。
    • 在多重和单值的线性回归中，各解释变量系数的量级大致相同，具有相互验证的效果，因此都可以考虑建立相应的先验模型。
    • 合并类别后的有序Logistic回归不仅消除了分离的影响，还在系数量级上与原生回归相互验证，因此选择将与“打包数值”有关的回归指标和回归方程纳入多因素模型的逻辑模型中。

  • 回归方程分析：

    • 在三个相关系数中，极大值0.749并不算突出，且与0.665相差不大；在三个决定系数中，极大值0.561并不算突出，且与0.442相差不大。以上现象表明，三个变量中不存在能够解释或预测排名数据绝大部分变异的自变量，因此对于单变量回归来说，应该建立同时包含这三个变量的综合模型，而非以某个变量为代表的特征模型。
    • 评分变量的回归方程有两个，区别在于是否剔除了离群值。对于预测模型来说，因为主要参考的是一般性情况，且优先考虑最优预测效果，所以更倾向于采用剔除离群值后的拟合度更高的回归方程；对于解释模型来说，剔除前后各有优劣，因此只要各系数和常数在量级上差别不大，两种方程就都可以采用。虽然排名生成模型主要承担的是解释功能，但也承担部分预测功能，因此选择采用剔除离群值后的回归方程参与单因素综合模型的构建。
    • 三个自变量中，每一个都能拟合出单独的回归方程。倘若我们假设三个单变量的回归方程具有同等效力，则可以使用其结果的算术平均值来表示对排名数据的解释和预测；但如果我们考虑到决定系数对单变量解释度的表征，则应该使用三个回归方程结果的加权平均值来表示对排名数据的解释和预测。值得一提的是，三个决定系数之和（1.028）近似为1，其中两个决定系数之和（1.003）几乎为1，表明这三个变量（尤其是其中的两个变量）是建立TOP排名表的绝对参考，其余变量则更多承担的是对项目属性的表征，这与各相关系数的大小顺位是完全一致的。

经过以上聚合分析，我们可以提炼出三类（总计7种）先验模型：

  1. 标准权数模型：
    1. 相关模型：$【y=0.749a_s+0.665b_s+0.157c_s】$
    2. 决定模型：$【y=0.561a_s+0.442b_s+0.025c_s】$
    3. 回归模型：$【y=0.601a_s+0.453b_s+0.022c_s】$
  2. 多因素模型：
    1. 线性模型：$y=163.382a+7.791*10^{-5}b+0.101c-1588.651\Longrightarrow【y=163.382a+7.791*10^{-5}b+0.101c】$
    2. 逻辑模型：$logit(P)=16.475a+1.178*10^{-5}b+0.009c+172.476\Longrightarrow【y=16.475a+1.178*10^{-5}b+0.009c】$
  3. 单因素综合模型：
    1. 算术模型：$y=1/3*[(220.694a-1840.537)+(83.762logb-986.141)+(-0.446c+1016.946)]\Longrightarrow【y=220.694a+83.762logb-0.446c】$
    2. 加权模型：$y=[0.561*(220.694a-1840.537)+0.442*(83.762logb-986.141)+0.025*(-0.446c+1016.946)]/1.028\Longrightarrow【y=123.81a+37.02logb-0.01c】$

说明：

  • 尽管回归方程中的 $y$ 确实来源于响应变量，但排名生成模型中的 $y$ 并不是“排名系数”或“打包数值”，而是一种可以作为排序依据的数值变量，统称为“排序数值”。
  • 虽然包含常数项的回归方程可以直接输出排名类数据的近似值，但就构建排名生成模型而言，这些近似数值在效度上与排序值没有根本区别，且会产生更多的误差和计算冗余，因此这里不将这些方程纳入模型而仅作展示用。
  • 由于回归模型采用的标准化系数来源于多重线性回归，因此其和线性模型的排名拟合结果在理论上应该完全相同。这一点可以用来检验变量的标准化是否成功以及这两个模型的拟合结果是否正确。

接下来，我们使用Excel对这7个先验模型进行效度验证。

我们首先使用公式【=STANDARDIZE(B2,AVERAGE(\$B\\$2:\$B\\$251),STDEV.P(\$B\\$2:\$B\\$251))】对三个自变量进行标准化，然后利用标准化了的变量来生成标准权重模型的排序数值。其它模型则使用原生变量即可。

得到了一系列排序数值后，我们使用RANK函数【=RANK.EQ(H2,\$H\\$2:\$H\\$251)】对数列进行降序排名并附值，所得结果即为模型拟合的排名数据。

为了验证模型效度，我们需要将拟合的排名数据与原生（标准）排名数据进行比对，通过各模型结果与真实值的接近程度来选择最优模型。

比对方法有两种，一是图像观察，二是偏差量化。

就图像观察而言，我们使用柱形图和折线图来生成观察对象。具体来说：

  1. 将七个模型的排名数据与标准排名数据按位对应，生成柱形图并进行堆积呈现，所呈现的值为各排名数据占对应总值的百分比。通过观察各位点的占比分布，即可比较各模型与标准模型的接近程度。
  2. 将原生排名数据的折线图作为标准趋势线，利用表格转置后的切片器将七个模型的数据折线图分别写入图像并与之对照。通过观察模型趋势之于标准趋势的波动性，即可比较各模型与标准模型的接近程度。

%E6%8E%92%E5%90%8D%E6%95%B0%E6%8D%AE%E7%99%BE%E5%88%86%E6%AF%94%E5%A0%86%E7%A7%AF%E5%9B%BE.png

在“排名数据百分比堆积图”中，若数据段（位点集）内的堆积情况较为平稳，占比分布均匀，则说明该段内的模型拟合情况均良好，不用进行比对；若数据段内的堆积情况不稳，占比分布明显不均匀（尤其是标准数据异常），则说明该段内的模型拟合情况存在较大差异，需要重点进行比对。

我们应当尤其关注标准数据的异常位点（有明显的高于或低于表现），具体原因如下：

  1. 在总体趋势平稳（即各模型具有基础效度）的情况下，当标准数据的位点不存在异常时，占比的分布可能不均匀；但当标准数据的位点存在异常时，占比的分布一定不均匀。
  2. 标准数据的柱形图集一端对齐（下方紧贴水平坐标轴），这样更有利于异常值的突出显示。具有同样性质的还有加权模型数据的柱形图集。

注意：数据越小，相同差异造成的占比影响就越大，这也是数据集前端部分分布明显不均的原因之一。此时我们只需重点关注标准数据集中明显异常的位点即可，不必把太多精力放在对整段占比分布的分析上。

观察该图可以看出，在标准数据占比较为突出的那几个位点和位点集中（18，37，59，73-79，135，148-157，187-211，223-247），算术模型和加权模型基本表现出了相同的占比趋势，其中算术模型要更加接近；逻辑模型的占比在某些情况下也会趋于相同，但在更多情况下还是和其余模型一起表现出相反的占比趋势。

根据以上观察结果，我们初步判断算术模型和加权模型具有较大优势，逻辑模型则具有一定优势。

%E6%A8%A1%E5%9E%8B%E8%B6%8B%E5%8A%BF%E6%AF%94%E5%AF%B91-2.png

%E6%A8%A1%E5%9E%8B%E8%B6%8B%E5%8A%BF%E6%AF%94%E5%AF%B92.png

在“动态趋势对比图”的各组合中，模型之于标准趋势的波动越明显，其现实拟合性也就越缺乏。

观察该图，可以看出标准权重模型和多因素模型的波动性要明显大于单因素综合模型，其中逻辑模型的波动相对最小；在单因素综合模型中，算术模型的波动性要相对小于加权模型的波动性，且各波动点的共变趋势更强。

根据以上观察结果，我们可以进一步判断算术模型和加权模型具有明显优势，其中算术模型优势更大。

经检验，回归模型和线性模型的趋势线几乎完全相同，说明变量的标准化和这两个模型的结果生成没有问题。

就偏差量化而言，我们使用拟合排名与标准排名的对位差值的平方和来表示“偏差量”，使用平均偏差量的开方来表示“偏差度”，公式如下：

  • 偏差量公式：【=SUMXMY2(\$A\\$2:\$A\\$251,I2:I251)】
  • 偏差度公式：【=SQRT(SUMXMY2(\$A\\$2:\$A\\$251,I2:I251)/250)】

“偏差量”之所以使用差值的平方而不是绝对值，是因为平方不仅能避免正负相抵，还能放大偏差的程度，从而有利于计算结果间的相互比较。

“偏差度”作为数据平方后的开方，量纲和各排名数据保持一致，既可视为对平均偏差的一种放大式替代，亦可以用于对差值分布的描述。

作为一种对模型数据与标准数据之差异的量度，模型的有效性与其偏差量或偏差度的大小成反比，我们的目的即是寻找偏差度较小的那些模型。

经计算，各模型的偏差量化指标如下：

相关模型 决定模型 回归模型 线性模型 逻辑模型 算术模型 加权模型
偏差量 202676 204338 212112 212186 167496 88410 106200
偏差度 28.473 28.589 29.128 29.133 25.884 18.805 20.611

可以看出，相关模型、决定模型、回归模型和线性模型的偏差度十分接近，都位于29左右；逻辑模型的偏差度与它们相比下降了一个档次，位于26左右；算术模型和加权模型的偏差度处于最低档次，位于20左右。

尽管同处于最低档位，算术模型的偏差度仍然要比加权模型低2个单位左右。结合前面对图像的观察结果，我们可以得出七个模型中的最优后验模型为单因素综合模型中的算术模型，即：

$y=220.694a+83.762\ln b-0.446c$，其中 $y$ 为排名数据，$a$ 为评分数据，$b$ 为评价人数数据，$c$ 为年份数据。

这便是各指标对TOP排名的最终影响模型。

最后还有几点需要说明：

  1. 我们预设了排名数据为相关变量的因变量，该预设基于这样一种业务逻辑：TOP排名表建立在各电影数据生成之后，倘若其建立目标中包含对某种客观现实的反映，则其内容必然基于某些已存在的指标。
  2. 建表时变量的影响是单向的，但在TOP排名表建成之后，其内容便随着展现和传播开始融入用户的认知，对他们的行为产生影响，包括但不限于排名靠前的电影评论人数增长得更快、高排名电影遭到盲目跟风和恶意干扰、小众电影受到大众观点的审阅进而产生指标的方向性变化等，这些都会进一步影响排名系统的效果（该排名是会定期更新的）。总的来说，指标数据影响排名的建立，排名建立后的曝光效应影响指标数据的变化，变化后的指标数据再次影响排名的顺位，这种反身性和交互性意味着数据通常不会单纯地反映客观现实，而是在其中包含了用户的主观建构。
  3. 就服务于等级模型的解释型回归而言，对某些指标（如正态性、等方差性等）的要求可以不是很严格。

数据探索

返回导航

对数据所能反映的深层性质进行探索和挖掘。

电影的“黄金年份”

从1931到2020，近百年的时间内诞生了无数部电影。豆瓣TOP排行榜所展现的，正是其中相对优秀的250部电影。

从数量的角度来衡量，1994、2004和2010三个年份并列第一，但仅仅如此是不足够的。

即便同属于优质电影的范畴，在250个等级顺位面前也不能一概而论。倘若数量相同而质量偏低，那么相应的评价也理应有所不同。

不同顺位之间，优质亦有区别。为了在考虑到数量产出的同时兼顾这种权重性，我们使用年份下排名系数的求和聚合结果来衡量该年份优秀电影产出的整体状况。

若是能藉此方法找出一个在优质电影产出上既有数量又有质量的年份，那么称其为电影的“黄金年份”便也不为过了。

我们使用Excel数据透视表聚合各年份下的排名系数总和与评分总和，以排名系数总和为降序指标进行排序并作图，结果如下：

%E9%BB%84%E9%87%91%E5%B9%B4%E4%BB%A3.png

排名系数聚合项是主要参考指标，评分聚合项是辅助检验指标。对比折线图可以看出，尽管有所波折，评分聚合值在整体上依然随排名系数聚合值的下降而下降，说明作为主要参考指标的排名聚合项是具有一定效度的。

进一步观察折线图，可以看出“1994、2010、2004”三个年份无论就哪个指标而言都具有显著优势，这与数量视角的衡量结果是一致的；在这三个年份中，1994年的两个指标都是最高，且在主要参考指标上尤为突出，高出第二名近20个百分点（在辅助检验指标中，这个数字是2.5）。

根据以上分析结果，我们可以得出电影的“黄金年份”为1994年，具体来说，这是一个诞生了《肖申克的救赎》、《阿甘正传》、《活着》、《这个杀手不太冷》、《低俗小说》、《九品芝麻官》等经典作品的年份。

1994.jpg

概述呈现与短时记忆的关系

在之前的数据处理环节中（1.1.2），我们对电影概述部分所包含的信息组块进行了定义（文字为1，标点为0.5），并通过这些定义计算出了一系列具体的组块数值（向下舍入）。

接下来，我们使用Excel数据透视表对各组块数值进行频数聚合，排除组块数为0（即概述不存在）的项并按升序排列，最终结果如下表所示：

%E4%BF%A1%E6%81%AF%E7%BB%84%E5%9D%97%E5%B3%B0%E8%B0%B7%E5%9B%BE.png

根据表中数据绘制组块数类别与频数的折线趋势图，发现该图呈现出较为明显的起伏态势，其中“5-9”、“12-16”、“22”处为明显峰态，其余部分为非峰态或谷态。

对峰态类进行频数统计和频率计算，结果如下表所示：

峰态类组块范围 分布频数 分布频率
5-9 75 31.6%
12-16 70 29.5%
22 8 3.4%
总计 153 64.6%

组块数类别共有34类，峰态类包含了其中的11类，约为全类别的三分之一；频数总数为237项，峰态类包含了其中的153项，约为总数的64.6%。

峰态类是数据集中趋势的体现，在该数据集中，三分之一的类别聚合了三分之二的数量。这种集中趋势的产生原因，与人类的短时记忆容量有关。

短时记忆是从感觉记忆到长时记忆的中介环节，其容量又称为短时记忆广度。短时记忆容量的基本信息单位是组块（chunk），组块的数量为7±2（即平均值为7，标准差值为2），这个数据来源于美国心理学家米勒（G.Miller，1956）发表的论文：《神奇的数字7±2：我们信息加工能力的限制》，其信度在各类材料上和各种文化间都得到了证实。

若干短时记忆按时间顺序组合到一起，就形成了名为“工作记忆”的连续加工系统，其容量围绕平均短时记忆容量的倍数而波动，倍数越大，波动程度越小。

组块并非具有绝对大小的单元，而是会随着个人经验变化而变化的结构。倘若抽取长时记忆中的知识经验来对刺激材料进行组织和再加工，就能将若干信息单位联合成更大的组块，从而达到扩充短时记忆容量的目的，因为短时记忆容量的单位始终是组块而非比特。

根据该性质可以得出，该分析项目中的组块定义并不具有在时间跨度上的普适意义。随着短时记忆中藉由内部言语进行加工的复述和存储的循环发生，个体将依据各自的经验储备和认知方式来对组块进行再编码，这就会造成组块数目的特异性变动。

也就是说，该项目中以字符为组块单位的划分方式更多针对的是初次加工概述材料的信息场景。倘若令刺激材料的组块数在7及其倍数的合理范围内波动，就能够在保证内容完整的情况下充分利用短时记忆的暂存容量，从而达到识记轻松和印象深刻的最佳结合点。

通过频数分布表可知，占比最高的“5-9”属于7±2的范畴，其余类别则围绕7的倍数波动（“12-16”在14(7x2)附近，“22”在21(7x3)附近），也就是说全部概述项中有三分之二的项目都符合这种信息需求。

虽然这种特征有利于概述项的信息呈现和加工，但并不一定是有意而为之，还有可能是相关编辑人员在保持工作记忆的状态下流露出了对这种数量结构的天然依赖，亦有可能是相关审核人员在最后的敲定环节中表现出了对这种数量结构的自然青睐。

总而言之，TOP榜单中的概述项具有符合短时记忆和工作记忆容量的信息加工特征，但这种特征的出现并不一定完全依赖于外显的意识。

二. 标注项目数据分析

跳转至项目1

数据背景：

  • 标注形式：每个项目文件中主要有两列数据，一列为房地产销售过程中的录音文本，一列为针对该文本的标注；
  • 标注规则：理解文本并对照标准话术中的关键词条，如果匹配（击中），则将关键词条写入标注列

项目目标：

  • [x] 分析标注速度
  • [x] 分析标注难度
  • [x] 分析文本质量
  • [x] 分析文本质量与标注难度的关系
  • [x] 制作自动化周报与月报

数据获取与整理

返回导航

使用Python聚合数据

In [ ]:
import pandas as pd
import os
import re
import time

pattern=re.compile(':(.+)\|') # 提取标注有效内容的正则表达式
In [ ]:
def agg(path): # 聚合模块：通过文件路径返回聚合信息
    
    df=pd.read_excel(path)
#     print(df.columns)

    text=df['text']
    biaozhu=df['标注']
    
    sumti=df.shape[0]-1 # 文本单元数
    sumt=0 # 文本总长度
    sumbi=0 # 对标次数
    sumb=0 # 标注总长度
    
    for t in text:
#         print(str(t))

        if str(t)=='nan' or str(t)=='': # 忽略异常项
            continue
        else:
            sumt=sumt+len(str(t)) # t包含float数据不可遍历或使用len
            
    for bz in biaozhu:
#         print(str(bz))

        if str(bz)=='nan' or str(bz)=='':
            continue
        else:
            result=re.findall(pattern,str(bz)) # 通过正则表达式返回标注的有效内容（对标）
            sumbi=sumbi+len(result) # 通过项目树记录对标次数
            for r in result:
                sumb=sumb+len(r)
                
    return sumti,sumt,sumbi,sumb
In [ ]:
def ratindi(path): # 比率指标模块：提取关于有效单元内匹配率的描述性指标
    df=pd.read_excel(path)
    textbz=df[['text','标注']]
    ratlist=[] # 匹配率列表

    for tb in textbz.itertuples(): # 按行迭代，析出三元元组
    #     print(tb[0],tb[1],tb[2])

        result=re.findall(pattern,str(tb[2]))
        
        sumr=0 # 储存单元内标注的有效内容长度
        for r in result:
            sumr=sumr+len(r)
            
        rat=sumr/len(str(tb[1])) # 计算单元内匹配率
    #     print(tb[0],rat)
    
        if rat!=0 and rat<1: # 将非0且小于100%的匹配率视为有效单元内匹配率
            ratlist.append(rat) # 存入ratlist
            
            
#     print(ratlist)
    vali=len(ratlist) # 有效单元数
    dsb=pd.Series(ratlist).describe() # 对匹配率列表进行描述性统计
#     print(dsb)
#     print(dsb[1],dsb[2],dsb[5]) 
    
    return vali,dsb[1],dsb[2],dsb[5] # 返回有效单元数，mean，std，50%三个指标
In [ ]:
database=[]
i=0

for root,dir,file in os.walk(r"E:\语义理解标注\房地产销售流程\Database"):
    # 首次遍历结果为子文件夹路径下的各子文件夹名称，2次遍历开始包含子文件内部文件名

    for name in file:
        data=[] # 临时存储单元
        path=os.path.join(root,name)
        
#         print(time.ctime(os.path.getctime(path)))
        print(path,agg(path),ratindi(path))

        data.append(agg(path)[0]) # 添加文本单元数
        data.append(agg(path)[1]) # 添加文本总长度
        data.append(agg(path)[2]) # 添加对标次数
        data.append(agg(path)[3]) # 添加标注总长度
        data.append(ratindi(path)[0]) # 添加有效单元数
        data.append(ratindi(path)[1]) # 添加平均匹配率（有效单元内）
        data.append(ratindi(path)[2]) # 添加匹配率的标准差
        data.append(ratindi(path)[3]) # 添加匹配率的中位数
        data.append(path) # 添加文件路径相关信息

        database.append(data) # 将一组数据存入database
        
        i+=1
        print('[%d complete]'%i)
In [ ]:
database=pd.DataFrame(database,columns=['文本单元数','文本总长度','对标次数','标注总长度','有效单元数','平均匹配率','匹配率标准差','匹配率中位数','路径信息'])
database.to_excel('./source/BZdata.xlsx',index=False)
print('complete')

处理完成，效果如下： BZdata.jpg

使用Excel对聚合后的数据作进一步处理

  • 添加日期列，用来表示处理文件的时间。
  • 添加批次列，用来表示分项目的完成批次。
  • 利用文本总长度和标注总长度计算“加权匹配率”【=F2/D2】
  • 计算每个批次内各平均匹配率的均值和标准误：
    • 均值公式：=AVERAGE(H2:H11)
    • 标准误公式：=STDEV.P(H2:H11)
  • 计算单个项目文件内各单元匹配率和整个项目批次内各平均匹配率的变异系数：
    • 变异系数公式：=STDEV.P(H2:H11)/AVERAGE(H2:H11)
  • 修正表格的视觉效果，方便查看。

处理完成，效果如下： BZdata-new.jpg

数据分析

返回导航

标注速度

结算薪资时需要参考文本标注的工作量，标注速度即为工作量和工作时间的比值。通常情况下，我们采用文本总长度来衡量工作量的大小，因为这样最符合客观逻辑，也最容易为人所接受。

但这并不能描述整个标注过程中所有标注人员对工作量的感知，这一点将在后面的章节中加以讨论。就目前对标注速度的计算而言，我们直接使用最普遍的指标即可。

使用SUMIF函数统计某一批次内标注文本的总长度，使用COUNTIF函数统计该批次的总标注天数，两者的比值即为标注的速度（以天为单位）。

速度公式：=SUMIF(Sheet1!$B$2:$B$59,A2,Sheet1!$D$2:$D$59)/COUNTIF(Sheet1!$B$2:$B$59,A2)

具体计算结果见下表：

批次 标注文本长度 标注天数 标注速度(字/天)
1 211814 10 21181
2 70472 11 6407
3 240893 17 14170
4 308090 20 15405

可以看出第一批次的标注速度最快，第二批次的标注速度最慢，第三批次和第四批次的标注速度差不多。

标注难度

文本长度越长，文本中的匹配点越少，文本标注的难度就越大。

因此我们使用匹配率来表示标注难度，匹配率越低，标注难度越大。

已知标注难度 $d$ 为匹配率 $m$ 的函数，且$d(m)$满足以下公理化条件：

  1. $d(m)$为$m$的递减函数：匹配率为标注难度的负向指标。
  2. 当匹配率为0%时，难度无穷大：此时不可能完成任何标注。
  3. 当匹配率为100%时，难度为0：此时不存在任何多余文本。
  4. $d'(m)$为$m$的递增函数：随着匹配率的增加，标注点的增多，中央凹外视野的重叠几率增加，视觉冗余变多，导致难度下降的边际效用逐渐递减，进而导致曲线在对应点处切线的斜率逐渐增大。

不难得出，满足以上公理化条件的函数形式为：$d(m)=-\log m$

代入不同的匹配率，即可得到不同的难度数值。

匹配率分为“平均匹配率”和“加权匹配率”。这两个指标都表示标注文本占总文本的比例，但在具体含义上有所不同。

平均匹配率指的是在排除了无标注单元后，剩下的有效单元中各单元内匹配率的平均值。

加权匹配率指的是以各单元文本长度为权数，以单元文本长度和总文本长度的比值为权重，求各单元内匹配率的加权平均值（在数值上等于标注总长度与文本总长度的比值）。

在熟悉文本后，由于积累了相应的标注经验，可以在很短的时间内筛选出一段文本的标注点并忽略无效单元；由于形成了知觉的恒常性，即便文本形态发生改变也依然能够通过行文环境和自身经验来稳定识别。在这种情况下，各单元文本长度尽管有所不同，但都成为了一个个被识别的组块单位，均具有对于匹配率的基础参考度，因此更适合采用算术平均匹配率（即各单元权重相等的加权平均）。

而在熟悉文本前，由于缺乏经验和知觉的辅助作用，我们需要逐步遍历全部文本并进行地毯式标注对照。在这种情况下，单元的文本长度越长，其对匹配率的参考程度也就越大，因此更适合采用加权平均匹配率（即将全部单元合并后的单元匹配率，此时能够形成对匹配率的完全参考）。

总的来说，两种匹配率都具有其参考价值。之所以选择平均匹配率下的各匹配率进行标准差和中位数的分析，是因为加权匹配率并不存在相应的数据来源。

接下来，我们对两种匹配率指标在各批次内的性质和特征进行描述性统计分析。结果如下表所示：

%E5%8C%B9%E9%85%8D%E7%8E%87-2.jpg

我们采用均值作为描述集中趋势的指标，公式为：AVERAGEIF(Sheet1!$B$2:$O$59,G3,Sheet1!$H$2:$H$59)；

我们采用标准误作为描述离散趋势的指标，公式为：=stdif(Sheet1!$B$2:$O$59,G3,7)；

我们采用变异系数作为描述离散程度的指标，公式为：=I3/H3。

由于Excel并未提供类似于SUMIF、AVERAGEIF、COUNTIF等可以对取值条件进行定义的标准差计算函数，因此我们使用VBA代码来编写符合要求的自定义函数stdif来用于标准误的计算：

vb
Function stdif(Arr As Range, m As Integer, n As Integer) '自定义stdif函数（范围，条件，位置）

    Dim v(1 To 100) As Variant '一维数组变量

    For i = 1 To Arr.Rows.Count '遍历首列单元，提取符合条件的固定位置元素
        If Arr.Cells(i, 1) = m Then
            v(i) = Arr.Cells(i, n)
        End If
    Next

    stdif = WorksheetFunction.StDev_P(v) '计算标准差

End Function

为了方便数据的观察和分析，我们作出各批次在两种匹配率指标下的折线数据图：

%E5%8C%B9%E9%85%8D%E7%8E%87%E6%8C%87%E6%A0%87.png

根据图像可以看出，四个批次在两种匹配率指标下具有相似的变动趋势，可以结合两种指标对各批次的标注难度进行综合分析：

  1. 第一批次的匹配率均值最低，标准误最低，变异系数偏低，说明该批次的匹配率要显著低于其它批次，且其内部差异最不明显，也就是说其匹配率的低下不仅具有显著性，还具有普遍性；

  2. 第二批次的匹配率均值最高，标准误偏高，变异系数偏低，说明该批次的匹配率要显著高于其它批次，且其内部差异并不算特别明显；

  3. 第三批次的匹配率均值一般，标准误一般，变异系数一般，说明该批次的匹配率和内部差异都不算突出；

  4. 第四批次的匹配率均值一般，标准误偏高，变异系数最高，说明该批次的匹配率虽然不算突出，但其内部差异要显著高于其它批次，因此均值的代表性也最差。

根据以上分析，我们认为第一批次项目的标注难度最高，第二批次项目的标注难度最低，第三批次项目的标注难度略低于第四批次（胜在稳定）。

为验证哪个匹配率指标更符合这一结果，我们计算出各项目的两种标注难度后在批次上对其均值进行聚合，得出结果如下所示：

image.png

可以看出，与结论最接近的难度指标是算术难度。虽然加权难度的差别并不算大，但相比之下我们最好使用算术匹配率来参与标注难度的计算。

工作量描述：信息熵与因子分析

计算标注速度时，我们使用了文本总长度来表示标注工作量的大小，因为这种衡量方式具有客观普遍性。

但普遍性不等于唯一性。理论上，我们有五种指标可以用来衡量工作量，分别为：文本单元数、文本总长度、对标次数、标注总长度和有效单元数。它们从各自的角度出发表达了对“工作量”这一概念的理解，具有相对客观性和理论存在性，“文本总长度”是其中最具代表性的一个。

① 信息熵

既然这五种指标都具有一定的客观依据，但又不能充分描述被描述对象的状况，那我们至少应该找到某种方法，使得我们对其各自的描述能力能有一个量化的、具体的感知。

指标的描述能力与数据集的不确定性有关。数据分布的差异越大，数据集的平均不确定性越低，越能体现该指标对各数据项的区分度，即说明其描述能力越强。

为了衡量这种性质，我们可以计算各指标项下数据集的信息熵。在此之前，先要消除组间差异中由量纲所决定的那一部分。

观察这五个指标的量纲和方向，结果如下表所示：

文本单元数 文本总长度 对标次数 标注总长度 有效单元数
量纲 单元格 字符 标注项 字符 单元格
方向 正向 正向 正向 正向 正向
  • 说明：量纲（dimension）借鉴了其在物理学中的定义，用来表示指标变量间不可转化且相互独立的生成方式。

各指标量纲不统一且方向都为正向，因此我们可以使用正向归一化来消除量纲间的差异。Excel公式如下：

  • =((Sheet1!C4-MIN(Sheet1!C$2:C$59))/(MAX(Sheet1!C$2:C$59)-MIN(Sheet1!C$2:C$59)))+0.00001
  • 添加0.00001常数项的目的为消除0值，防止计算信息量时报错。

接下来我们计算各数据项在数据集中出现的概率$p(x_i)$，即每单位指标分配到各数据项下的概率。公式如下：

  • =A2/SUM(A$2:A$59)

得到概率后，通过$H(X)=E[I(x_i)]=-\sum_{i=1}^np(x_i)logp(x_i)$即可计算信息熵。其中$I(x_i)=-logp(x_i)$代表的是某个事件发生（即某个数据项出现）时所包含的信息量，当$p(x_i)\rightarrow1$时，$I(x_i)\rightarrow0$；当$p(x_i)\rightarrow0$时，$I(x_i)\rightarrow\infty$。公式如下：

  • =-SUMPRODUCT(F2:F59,LOG(F2:F59,2))

信息熵越抵，平均信息量（以概率为权的加权平均）越低，为消除不确定性所平均需要了解的信息量就越少，即平均不确定性越低。正如前文所言，平均不确定性能够体现指标的描述能力，也就是说指标的信息熵越抵，其描述能力便越强。

根据以上分析，最终五个指标的信息熵和描述能力如下：

文本单元数 文本总长度 对标次数 标注总长度 有效单元数
信息熵 5.247 5.403 5.536 5.611 5.534
描述能力 5 4 2 1 3
  • 说明：描述能力为正向定序指标，取值区间为$[1,5]$。

可以看出，就信息熵而言，五个指标中描述能力最强的是“文本单元数”，其次是“文本总长度”。

文本总长度因其客观性和认可度已被普遍用于标注工作量的计算，根据信息熵的计算结果可以看出其对不同项目的描述能力也十分优秀。那么文本单元数呢？其作为一个在描述能力上甚至要优于文本总长度的工作量计算指标又有什么含义和意义呢？

通过对熟练标注作业进行经验分析，我们可以发掘出更多关于标注工作的细节。

在接手标注文件的初期，由于对文本内容和标注内容不甚熟悉，逐字遍历文本后与标注内容对照几乎是工作的常态。在这一阶段，用文本总长度来表征工作量是很合理的。

在处理标注文件的后期，由于已对标注内容十分熟悉，对文本结构和内容也有了明显预期，因此会在工作模式上产生以下变化：

  1. 从注意的角度来说，由于对标注活动各个环节的熟悉程度很高，且标注的手动操作在经过大量重复后近似于自动化，因此更容易将注意资源分配到对文本内容的信息加工上，导致加工效率的进一步提升；
  2. 从记忆的角度来说，由于加工效率的大幅提升和注意资源的大量释放，登记容量大但保持时间短的感觉记忆（主要是视觉编码）中的更多信息开始因为受到注意而得到了进一步加工，因此会在浏览文本时形成一种与拍照类似的“范围视觉”，从而缩小各单元间因为文本长度不同而产生的处理时间上的差异。

所以在这一阶段，用文本单元数来表征工作量是更为合适的。

总得来说，文本总长度和文本单元数是衡量工作量的两个理想指标，其中文本总长度是客观的、偏前期的，文本单元数则是主观的、偏后期的。

② 因子分析

因子分析通过对载荷矩阵施加因子轴旋转，扩展了主成分分析在专业上的可解释意义。具体而言，因子分析在提取公因子时不仅考虑了相关关系的存在，还考虑了相关关系的强弱，使得提取出来的公因子既能够起到降维作用，又能够很好地被已知变量所解释。

我们使用SPSS对五种工作量指标进行因子分析，具体过程如下：

%E5%9B%A0%E5%AD%90%E5%88%86%E6%9E%902.png

首先对分析条件进行判断。KMO检验的值为0.651>0.6>0.5，说明偏相关系数平方和较小，简单相关系数平方和较大，原始数据中存在公共因子；巴特利特球形度检验的显著性P=0.000<0.05，说明相关系数矩阵为非单位矩阵，各变量间不完全独立，具有一定的相关性。也就是说，该组变量适合进行因子分析。

公因子方差又称共同度，为各变量在各公因子上载荷的平方和，表示变量中的信息（方差）能够被公因子所解释的程度。可以看出倘若视变量包含信息为1，在进行公因子提取后，被表达的信息值在0.802到0.929之间，均大于0.7，说明各变量均能够很好地被公因子所表达，信息降维的质量很高。

image3.png

在总方差解释表中，我们主要关注旋转后的载荷平方和。基于特征值大于1的标准，我们提取出两个公因子，特征值分别为2.919和1.349，其占总特征值的百分比即为解释的总方差比率。可以看出，筛选出来的两个公因子共同解释了总变异的85.376%，保留了较多的原始变量信息，因此无需额外添加特征值。

各特征值所解释的方差占累积解释方差的百分比即为特征值的方差贡献率，可视为公因子的权重参与到综合因子的计算中。

%E5%9B%A0%E5%AD%90%E5%88%86%E6%9E%900.png

碎石图又名滑坡图，坡度越高的点重力势能越大。倘若某点之后的坡度趋于平缓，即斜率在该点处显著下降，则选取该点以上的特征值参与到因子分析中。观察图像可知，可选的特征点共有两个，符合特征值大于1的标准，我们将其命名为公因子1与公因子2。

%E5%9B%A0%E5%AD%90%E5%88%86%E6%9E%903.png

成分矩阵是旋转前的载荷矩阵，通过相关系数来表征变量对公因子的依赖程度。该矩阵可以通过除以对应主成分的特征根来消除主成分的权重不平等，所得矩阵即为主成分分析的系数矩阵。

旋转后的成分矩阵是因子分析所需要的载荷矩阵，可以看出“对标次数”、“标注总长度”、“有效单元数”在公因子1上有较高载荷，“文本总长度”、“文本单元数”在公因子2上有较高载荷，因此可以把公因子1解释为“标注工作量”，其处理对象为“标注”，处理方式为“对标”；把公因子2解释为“文本工作量”，其处理对象为“文本”，处理方式为“阅读”。也就是说工作量主要分为两个部分，一是浏览文本，二是对标。

%E5%9B%A0%E5%AD%90%E5%88%86%E6%9E%9001.png

这些性质也体现在旋转后的空间载荷图中。观察图像可以看出，5个因子在坐标系内聚成两类，围绕纵轴的两个因子在公因子2上具有较大载荷，围绕横轴的三个因子则在公因子1上具有较大载荷，各坐标点与旋转后的成分矩阵一一对应。根据图像，我们可以很直观地看出公因子1与公因子2主要代表了哪些变量。

值得一提的是，虽然两个公因子在载荷分布上具有明显的倾向性，但它们之间并不是完全孤立的。变量在某公因子上有较大载荷的同时也在另一个公因子上有一定载荷，具体的相对关系和影响方式可以在后面的表达式中得到展现。

image6.png

成分得分系数矩阵是生成表达式并计算公因子得分的主要依据，析出的方法为回归。

设“文本单元数”、“文本总长度”、“对标次数”、“标注总长度”、“有效单元数”标准化后的变量为$x_{1-5}$，公因子1和公因子2为$F_1$、$F_2$，可以得出公因子的得分表达式为：

  • 标注工作量：$F_1=-0.049x_1-0.157x_2+0.391x_3+0.437x_4+0.314x_5$
  • 文本工作量：$F_2=0.498x_1+0.549x_2-0.021x_3-0.231x_4+0.108x_5$

可以看出对各公因子影响较大的是在其上有较大载荷分布的变量（即归属于各解释类别的变量），但在其上载荷分布较小的变量也并非完全没有影响，其中比较明显的是 $F_2$ 中 $x_4$ 的系数-0.231（且该变量在公因子2上的载荷也为负值）。

该系数说明标注长度越长，浏览文本所花费的时间也会相对减少，其中介变量可能是匹配率（标注/文本）。根据上文可知，匹配率的增加会降低标注难度（$d(m)=-\log m$），从而减小浏览文本的负担。而标注长度越长，匹配率增加的概率也会越高。

两个公因子可以合并成一个综合因子，综合因子得分即为对完整工作量的描述（解释为“综合工作量”）。

合并时需要考虑各公因子权重，权重系数来源于总方差解释表中的旋转载荷平方和。尽管两个公因子的累积方差解释率为85.376%，但由于我们在实际研究中默认两个公因子即代表了全部的变量，因此视两公因子的总方差解释率为100%，各公因子权重即是其方差解释率（贡献率）的归一化系数。

设综合因子为$F$，其表达式为：

  • 综合工作量：$F=\dfrac{48.408\%}{85.376\%}F_1+\dfrac{36.968\%}{85.376\%}F_2$

接着我们将变量系数矩阵（5x2）和公因子权重矩阵（2x1）相乘，得出系数矩阵C（5x1），为各变量在综合表达式中的系数。

数组公式为：{=MMULT(M2:N6,M8:M9)}，结果如下：

image-4.png
据此得出 $F$ 的最终表达式为：$F=0.188x_1+0.149x_2+0.213x_3+0.148x_4+0.225x_5$，$F$ 为标准化了的数值。

对C进行归一化处理即可得到各变量的权重W，对C下各公因子内主变量系数分别进行归一化处理即可得到各变量的内权重W1（假设变量系数完全来自于公因子）。

设两公因子权重相等，利用数组公式{=0.5*(M2:M6+N2:N6)}可以得到系数C0，对系数C0全部进行归一化处理即可得到变量的等权权重。

将各权重整理成表格，结果如下：

文本单元数 文本总长度 对标次数 标注总长度 有效单元数
权重W 0.204 0.161 0.231 0.160 0.244
内权重W1 0.558 0.442 0.363 0.253 0.384
等权权重W0 0.244 0.213 0.201 0.112 0.229

与变量的信息熵表对比可以看出，在不考虑公因子综合的情况下，因子分析下的权重与信息熵所代表的描述能力有着一样的相对关系；在不考虑公因子权重的情况下，因子分析下的权重与信息熵所代表的描述能力有着近似的相对关系。

这种信息熵与权重的关系，为熵值法的存在提供了一定的理论依据。

综上所述，我们对于标注工作量的描述可以采取以下方法：

  • 客观情况下，我们使用文本总长度来描述工作量的大小。
  • 标注人员为熟练工时，我们使用文本单元数来衡量工作量的大小。
  • 需要全面考虑各种条件时，我们使用综合因子F来衡量工作量的大小。

文本质量的降维表征

“文本质量”是一个具有综合性质的指标，可以从两个角度来衡量，一是“有效率”，二是“均匀度”。

有效度：有效单元数占总单元数的比例，用“有效单元数”与“文本单元数”的比值来表示。有效度越高，标注文本所占用的时间比例越高，审阅无标注文本所占用的时间比例越少，因此文本质量越高。

均匀度：有效单元内匹配率的均匀程度（即匹配率的离散趋势的相反），用“平均匹配率”与“匹配率标准差”的比值（即决定系数的倒数）来表示。均匀度越高，标注节奏越平稳，越能说明文本单元的划分程度合理，因此文本质量越高。

分别求出两个质量指标在每个项目下的对应结果，更新后的表格如下图所示：

%E6%96%87%E6%9C%AC%E8%B4%A8%E9%87%8F%E8%A1%A8-2.jpg

为了验证各批次的文本质量，我们利用数据透视表对各批次下的文本质量相关指标进行平均聚合并作图，结果如下所示：

%E6%96%87%E6%9C%AC%E8%B4%A8%E9%87%8F%E5%9B%BE.png

无论从哪个指标来说，第二批次的文本质量都是最高。但就其它批次而言，我们还无法得出准确的相对关系。

为了得出更准确且更有效的结论，我们对“有效度”和“均匀度”这两个共同描述文本质量的指标进行客观赋权法，以期降维出一个能够起到综合评价作用的“文本质量”指标。

设有效度为 $a$，均匀度为 $b$，文本质量为 $y$。可采用的降维方法有变异系数法、熵值法和主成分因子分析法，接下来对它们逐一进行分析：

① 变异系数法

变异性越大，数据点间差异越大，数据分布的离散程度越高，数据集的信息量（方差）越大，特征和区分度越明显，其在多组数据集内的权重也就越大。

变异系数法的本质是比较数据集的离散程度。如果量纲完全一致，便可以直接使用标准差来衡量；如果量纲不一致，则使用消除了量纲差异的变异系数来衡量。

变异系数的Excel计算公式为=STDEV.P(T$2:T$59)/AVERAGE(T$2:T$59)，各指标权重大小为其变异系数的占比。

计算结果如下表所示：

有效度 均匀度
变异系数 0.669 0.308
权重 0.685 0.315

通过加权求和得出降维公式：$y=0.685a+0.315b$

② 熵值法

熵值法通过信息熵来确定权重。信息熵越小，随机性越小，平均信息量越少，不确定性越小，为消除不确定性所需要的信息量越少，就最大离散熵而言所提供的信息量越大，因此信息集的权重也就越大。

为排除量纲差异所带来的影响，需要先对数据进行无量纲化处理。经比较，极值熵值法为熵值法中的最优改进类型（朱喜安,魏国栋. 熵值法中无量纲化方法优良标准的探讨[J]. 统计与决策,2015(2)），故选用极值处理法（属于归一化的一种）来进行数据的去量纲化。

已知指标均为正向，则极值处理法的公式为：

  • =(T2-MIN(T$2:T$59))/(MAX(T$2:T$59)-MIN(T$2:T$59))+0.00001

计算各项目样本在指标下的出现概率，公式为：

  • =V2/SUM(V$2:V$59)

使用$E_j=-\dfrac{1}{\ln n}\sum\limits_{i=1}^np_{ij}\ln{p_{ij}}$计算各指标的归一化信息熵，公式和说明如下：

  • =-SUMPRODUCT(X2:X59,LN(X2:X59))/LN(COUNTA(X2:X59))
  • $\ln n$ 为最大离散熵定理下信息熵的等概率极大值，用来对信息熵的结果作归一化处理。
  • 这里计算信息量的log函数是以e为底的自然对数，因此信息熵的单位不是bit，而是nat。

为使信息熵与权重呈反比，我们使用$W_j=\dfrac{1-E_j}{\sum_{j=1}^m(1-E_j)}(j=1,2,...,m)$来计算权重，公式如下：

  • =(1-X60)/(COUNTA($X$60:$Y$60)-SUM($X$60:$Y$60))

最终求得两个指标的信息熵和权重如下表所示：

有效度 均匀度
信息熵 0.915 0.957
权重 0.665 0.335

降维公式：$y=0.665a+0.335b$

可以看出数据集的变异性与信息熵紧密相关。

③ 因子分析

当变量数量为两个时，简单相关系数等于偏相关系数，即$\sum\sum_{i\neq j}r_{ij}^2\equiv\sum\sum_{i\neq j}r_{ij.k}^2$，KMO值恒等于0.5，因此不适合进行主成分分析与因子分析。

综上所述：

变异系数法求得的结果与熵值法很接近，因此我们选用更加严谨的熵值法来确定文本质量的降维公式，即：$y=0.665a+0.335b$

其中有效度为 $a$，均匀度为 $b$，文本质量为 $y$，均为无量纲数据。

值得一提的是，只有共同描述同一性质的指标才可以合并，分别描述同一性质的指标则不可合并，仅可比较。这便是我们对描述标注难度的两种匹配率不进行合并（而只进行比较）的主要原因。

相关矩阵分析

根据上文所求公式计算“算术难度”、“综合工作量”和“文本质量”，所得表格如下所示：

image-2.png

我们使用Pearson相关来计算各变量间的相关系数，具体原因如下：

  • 除计数数据外均为连续变量，且计数数据近似于连续变量；
  • 所有变量均为等比数据；
  • 各变量下数据个数均大于30；
  • 根据中心极限定理，各变量总体均为正态分布；
  • Pearson相关本质上是测量两个变量的线性关系的方向和程度，如果以此为目标那么在任何情况下都可以使用，区别只在于线性程度的大小。

经过SPSS分析和Excel转换，最终的相关矩阵表如下所示：

image-8.png

通过相关矩阵，我们可以很清晰地看出各变量之间的相关关系。

根据相关关系中的相关系数，我们既可以对已得出的公式进行验证，也可以为需要解决的问题提供参考。

影响标注速度的因素

标注速度以天为单位，描述对象为批次，分析影响因素时也需要将变量聚合到各个批次上。

从逻辑上来讲，如果自变量是文件属性，那么文件处理速度便是因变量，即因果关系是确定的。在这种情况下，我们只需要通过相关系数来确定影响的程度和方向即可。

一个变量所引用的其它变量越多，则该变量越具有代表性。根据这一标准，我们选择最具代表性的“综合工作量”、“文本质量”和“算术难度”三个变量来参与相关分析，批次内聚合方式为取平均，结果如下：

image-3.png

可以看出，算术难度与标注速度之间存在很高的正相关（0.903），文本质量和标注速度之间存在很高的负相关（-0.878），偏相关分析结果亦是如此。

根据已知因果关系，我们可以说，文本难度越大，标注速度越快；文本质量越高，标注速度越慢。其中介变量为匹配率，即标注点的密集程度，标注点越密集，对标所耗费的时间就越多，处理的文本总长度就越少，反之亦然。

这种反直觉的结论证明了只用文本总长度来衡量工作量的缺陷。如果工作量同时考虑了浏览文本和对标，那便有可能出现难度与速度成负相关（-0.4），质量与速度成正相关（+0.4）的情况：

image-5.png

综合工作量与标注速度之间的相关关系较弱（-0.326），其原因可以通过偏相关分析来探索：

  • 在控制文本质量后对这两个变量求一阶偏相关，其相关性会有所下降（-0.222）；
  • 在控制综合工作量后对文本质量和标注速度求一阶偏相关，其相关性几乎保持不变（-0.845）；
  • 在控制算术难度后对这两个变量求一阶偏相关，其相关性会显著下降（-0.177）；
  • 在控制综合工作量后对算术难度和标注速度求一阶偏相关，其相关性几乎保持不变（0.894）；
  • 在控制文本质量和算术难度后对这两个变量求二阶偏相关，其相关性无法确定（具体来说，两相关变量与两控制变量的线性回归无法得出有效结果，所得标准化残差完全相反）。

%E5%81%8F%E7%9B%B8%E5%85%B3-2.png

因此我们可以说，综合工作量与标注速度之间并不存在直接的联系，它在某些情况下表现出来的与标注速度的共变性主要来源于其它变量（尤其是算术难度）的影响，且这种影响是单向的。

这一结论也为“综合标注速度”这一因变量指标的存在提供了理论依据。

自动化图表

返回导航

使用Excel制作自动化图表，实现对源数据的动态调用和呈现。

相关表

自动化相关表可以根据选择的变量自动生成对应的相关系数。

首先利用数据验证建立所有变量的下拉列表，然后通过变量值返回相关系数。

我们用match函数获取单元格内容的序列号，用index函数返回对应序列号下的整列数据，用correl函数求两列数据的Pearson相关。

公式如下：

excel
=CORREL(
INDEX(Sheet1!$C$2:$AC$59,0,MATCH(相关表!Q2,Sheet1!$C$1:$AC$1,0)),
INDEX(Sheet1!$C$2:$AC$59,0,MATCH(相关表!R1,Sheet1!$C$1:$AC$1,0))
)

相关表效果如下：

周报

自动化周报可以依据所设置的起始日期自动修改所有内容，且不受源数据填写顺序的影响。

更新数据源后输入本周的开始日期，即可生成本周周报的全部内容；将去重后的日期列作为数据验证的序列建立下拉列表，即可查看不同周期下的内容表现。

自动化功能依赖于函数和条件格式，下面将对此进行展现。

周报标题的单位为“年月周”，各单位数值随起始日期的变动而变动。公式如下：

excel
=YEAR($B$1)&"年"&MONTH($B$1)&"月第"&ROUNDUP(DAY($B$1)/7,0)&"周"

项目进度为已完成的工作量，计算的是终止日期前的文本总长度，公式如下：

excel
=SUMIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
"<="&$D$1,
INDEX(Sheet1!$A:$AC,0,MATCH(A$8,Sheet1!$A$1:$AC$1,0))
)

项目进度项中采用了基于数据条的条件格式，其值为项目进度占总目标的百分比，范围为$[0,1]$。

总目标可以是计算全部的工作量（项目完成后），也可以是一个固定值（项目完成前）。若是计算，公式如下：

excel
=SUM(INDEX(Sheet1!$A:$AC,0,MATCH($A$8,Sheet1!$A$1:$AC$1,0)))

结果指标包含可变动的日期和各维度下的一系列工作成果，若当日没有项目内容则显示“无”，若有多个项目内容则求其总和。公式如下：

excel
=IF(
COUNTIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),$A14
)<>0,
SUMIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),$A14,
INDEX(Sheet1!$A:$AC,0,MATCH(C$13,Sheet1!$A$1:$AC$1,0))
),
"无")

聚合栏计算的是一个周期内的工作成果的总和。

结果指标表中采用了基于公式判断的条件格式，低于平均的单元格值将显示为红色。基础公式为=C14<AVERAGE(C$14:C$18)。

特征指标包含可变动的日期和各维度下的一系列项目特征，若当日没有项目内容则显示“无”，若有多个项目内容则求其平均。公式如下：

excel
=IF(
COUNTIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$23,Sheet1!$A$1:$AC$1,0)),$A24
)<>0,
SUMIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$23,Sheet1!$A$1:$AC$1,0)),$A24,
INDEX(Sheet1!$A:$AC,0,MATCH(C$23,Sheet1!$A$1:$AC$1,0))
)/
COUNTIF(
INDEX(Sheet1!$A:$AC,0,MATCH($A$23,Sheet1!$A$1:$AC$1,0)),$A24
),
"无")

聚合栏计算的是一个周期内的项目特征的平均。

周统计直接引用聚合栏中的结果，并展现相应指标在一周内的波动趋势。

周环比分为结果指标的周环比和特征指标的周环比。环比增长率=（本期数值-上期数值）/上期数值×100%=（本期数值/上期数值-1）×100%。

结果周环比的上期数值为对应指标的总和，公式如下：

excel
=A$9/
SUMIFS(
INDEX(Sheet1!$A:$AC,0,MATCH(A$8,Sheet1!$A$1:$AC$1,0)),
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
">="&($B$1-7),
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
"<="&($D$1-7)
)-1

特征周环比的上期数值为对应指标的平均，公式如下：

excel
=E9/
SUMIFS(
INDEX(Sheet1!$A:$AC,0,MATCH(E8,Sheet1!$A$1:$AC$1,0)),
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
">="&($B$1-7),
INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0)),
"<="&($D$1-7)
)*
SUMPRODUCT(
(INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0))>=$B$1-7)*
(INDEX(Sheet1!$A:$AC,0,MATCH($A$13,Sheet1!$A$1:$AC$1,0))<=$D$1-7)
)-1

这里使用sumproduct函数输出两种判断条件下逻辑值的乘积和，数值上等于对应指标的个数。

周环比项中采用了基于单元格值的条件格式，大于0显示绿色字体和图标，小于0显示红色字体和图标，等于0显示黄色感叹号。

特征分布图（九宫格）

自动化特征分布图可以根据不同的数据源绘制相应的九宫格（二维三段定序划分，共计九种特征类别），依据九宫格内的数据分布情况即可对不同指标下项目的总体特征进行把握。

基于数据验证建立各指标的下拉列表，动态提取各指标值作为数据源（不受源数据填写顺序的影响），公式如下：

excel
=INDEX(Sheet1!$A:$AC,ROW(C2),MATCH(C$1,Sheet1!$A$1:$AC$1,0))

利用max函数和min函数计算数据源的三分位点，绘制趋势图用于验证，效果如下：

依据数据源建立散点图，添加三分之一位点和三分之二位点为新的系列，将它们的误差线作为九宫格的内部划分线。

各点坐标可以随数据源变动而变化，坐标轴范围和标题的变化则需要用到宏代码。代码如下：

vb
Sub upgrade()
' upgrade 宏
' min,max,name
' 快捷键: Ctrl+u

    ActiveSheet.ChartObjects("图表 2").Activate

    ActiveChart.Axes(xlCategory).Select '选中水平轴
    ActiveChart.Axes(xlCategory).MinimumScale = [G5] '更新轴最小值
    ActiveChart.Axes(xlCategory).MaximumScale = [G2] '更新轴最大值
    ActiveChart.Axes(xlCategory).AxisTitle.Select '选中水平轴标题
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = [G1] '更新轴标题

    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = [H5]
    ActiveChart.Axes(xlValue).MaximumScale = [H2]
    ActiveChart.Axes(xlValue).AxisTitle.Select
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = [H1]

End Sub

将宏代码固定到表单控件，更改数据源后点击“更新”按钮即可完成图表的全部刷新。

为了获取更多观察信息，我们将批次列作为系列数据标签的值选择范围，在数据点右边生成关于其所属批次的标注。

甘特图

自动化甘特图可以依据各批次的起止日期与已完成天数生成相应的进度条，通过进度条的位置、状态以及各进度条间的相互关系，可以对项目随时间的进展状况进行高效评估与总体把握。

首先将源表设置为超级表，这样就可以满足新任务的实时添加和呈现。

依据项目批次提取起止时间，核心逻辑为用match函数提取各批次起止点处的序列号，依据序列号返回日期列中的对应值，提取方式不受源数据填写顺序的影响。

公式如下：

excel
开始日期（精确匹配）：
=INDEX(
INDEX(Sheet1!$A:$AC,0,MATCH("日期",Sheet1!$A$1:$AC$1,0)),
MATCH($C2,
INDEX(Sheet1!$A:$AC,0,MATCH("批次",Sheet1!$A$1:$AC$1,0)),
0))

结束日期（升序匹配）：
=INDEX(
INDEX(Sheet1!$A:$AC,0,MATCH("日期",Sheet1!$A$1:$AC$1,0)),
MATCH($C2,
INDEX(Sheet1!$A:$AC,0,MATCH("批次",Sheet1!$A$1:$AC$1,0)),
1))

对“开始日期”和“需要天数”建立堆积条形图，消除“开始日期”的颜色并将条形图倒置，调整坐标轴起始点后所呈现图像为进度条的整体框架。

为“开始日期”添加正偏差方向的水平误差线，误差量设置为“已完成天数”，调整各显示参数后所得图像即为完整的项目进度条。

“项目进度”中内容用于文字标注,其呈现公式为="项目进度："&ROUND([@已完成天数]/[@需要天数]*100,0)&"%"，通过support列的数据标签放置在图像中。

跳至顶部

头部CSS：

In [2]:
%%HTML
<style>
li a:hover {
  background-color: #111;
}
</style>

--END--